﻿Imports System
Imports System.Data
Imports System.Data.OleDb

Partial Class DeleteJobs
    Inherits System.Web.UI.Page

    Protected Sub btnFindJob_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFindJob.Click
        'Assume that access_db/Jobs.mdb exists in the root of hosting directory
        'Dim strDataSource As String = Server.MapPath("/access_db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the current directory
        Dim strDataSource As String = Server.MapPath("db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the parent directory of C: drive
        'Dim strDataSource As String = Server.MapPath("../access_db/Jobs.mdb")

        Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
                                        "Data source=" & strDataSource
        Dim objConnection As New OleDbConnection(strConnection)
        Dim selectSQL As String = "SELECT * FROM tblJobs WHERE jobId = " & Val(txtJobId.Text)
        Try
            objConnection.Open()
            Dim objCommand As New OleDbCommand(selectSQL, objConnection)
            Dim objReader As OleDbDataReader
            objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If objReader.HasRows Then
                While objReader.Read()
                    lblDate.BackColor = Drawing.Color.Empty
                    lblDate.Text = Trim(objReader.Item("jobDate").ToString)
                    lblIndustry.Text = Trim(objReader.Item("jobIndustry"))
                    'lblTitle.Text = Trim(objReader.Item("jobTitle"))
                    lblSpecTitle.Text = Trim(objReader.Item("jobSpecTitle"))
                    lblCity.Text = Trim(objReader.Item("jobCity"))
                    lblStatus.Text = Trim(objReader.Item("jobStatus"))
                    lblPayRate.Text = Trim(objReader.Item("jobPayRate"))
                    txtJobDescription.Text = Trim(objReader.Item("jobDescription"))
                End While
            Else
                lblDate.BackColor = Drawing.Color.Red
                lblDate.Text = "Record not found!"
                lblIndustry.Text = ""
                'lblTitle.Text = ""
                lblSpecTitle.Text = ""
                lblCity.Text = ""
                lblStatus.Text = ""
                lblPayRate.Text = ""
                txtJobDescription.Text = ""
                objReader.Close()
                Exit Sub
            End If
            objReader.Close()
            objConnection.Close()
            objConnection.Dispose()
        Catch ex As Exception
            Response.Write("Error: " & ex.Message)
        End Try
    End Sub

    Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        'Assume that access_db/Jobs.mdb exists in the root of hosting directory
        'Dim strDataSource As String = Server.MapPath("/access_db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the current directory
        Dim strDataSource As String = Server.MapPath("db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the parent directory of C: drive
        'Dim strDataSource As String = Server.MapPath("../access_db/Jobs.mdb")

        Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
                                        "Data source=" & strDataSource
        Dim objConnection As New OleDbConnection(strConnection)
        Dim deleteSQL As String = "DELETE FROM tblJobs WHERE jobId = " & Val(txtJobId.Text)
        Try
            objConnection.Open()
            Dim objCommand As New OleDbCommand(deleteSQL, objConnection)
            objCommand.ExecuteNonQuery()
            objConnection.Close()
            objConnection.Dispose()
            lblDate.BackColor = Drawing.Color.Red
            lblDate.Text = "Record deleted!"
            lblIndustry.Text = ""
            'lblTitle.Text = ""
            lblSpecTitle.Text = ""
            lblCity.Text = ""
            lblStatus.Text = ""
            lblPayRate.Text = ""
            txtJobDescription.Text = ""
        Catch ex As Exception
            Response.Write("Error: " & ex.Message)
        End Try
    End Sub

    Protected Sub ddlJobs_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlJobs.DataBound
        ddlJobs_SelectedIndexChanged(sender, e)
    End Sub

    Protected Sub ddlJobs_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlJobs.SelectedIndexChanged
        'Assume that access_db/Jobs.mdb exists in the root of hosting directory
        'Dim strDataSource As String = Server.MapPath("/access_db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the current directory
        Dim strDataSource As String = Server.MapPath("db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the parent directory of C: drive
        'Dim strDataSource As String = Server.MapPath("../access_db/Jobs.mdb")

        Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
                                        "Data source=" & strDataSource
        Dim objConnection As New OleDbConnection(strConnection)
        Dim iJobId As Integer = Val(ddlJobs.SelectedValue)
        Dim selectSQL As String = "SELECT * FROM tblJobs WHERE jobId = " & iJobId
        Try
            objConnection.Open()
            Dim objCommand As New OleDbCommand(selectSQL, objConnection)
            Dim objReader As OleDbDataReader
            objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If objReader.HasRows Then
                While objReader.Read()
                    lblDate.BackColor = Drawing.Color.Empty
                    lblDate.Text = Trim(objReader.Item("jobDate").ToString)
                    lblIndustry.Text = Trim(objReader.Item("jobIndustry"))
                    'lblTitle.Text = Trim(objReader.Item("jobTitle"))
                    lblSpecTitle.Text = Trim(objReader.Item("jobSpecTitle"))
                    lblCity.Text = Trim(objReader.Item("jobCity"))
                    lblStatus.Text = Trim(objReader.Item("jobStatus"))
                    lblPayRate.Text = Trim(objReader.Item("jobPayRate"))
                    txtJobId.Text = (objReader.Item("jobId"))
                    txtJobDescription.Text = Trim(objReader.Item("jobDescription"))
                End While
            Else
                lblDate.BackColor = Drawing.Color.Red
                lblDate.Text = "Record not found!"
                lblIndustry.Text = ""
                'lblTitle.Text = ""
                lblSpecTitle.Text = ""
                lblCity.Text = ""
                lblStatus.Text = ""
                lblPayRate.Text = ""
                txtJobDescription.Text = ""
                objReader.Close()
                Exit Sub
            End If
            objReader.Close()
            objConnection.Close()
            objConnection.Dispose()
        Catch ex As Exception
            Response.Write("Error: " & ex.Message)
        End Try
    End Sub

End Class